<?php
namespace app\index\controller;

use app\common\model\attendDid;
use app\common\model\attendScheduling;
use app\common\validate\FileUploadValidate;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use think\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Validate;
use think\Db;

class ExcelController
{
    private $attendDidModel; // 临时部门表
    private $attendSchedulingModel; // 排班表
    public function __construct()
    {
        $this->attendDidModel = new attendDid();
        $this->attendSchedulingModel = new attendScheduling();
    }
    // 导入临时员工表
    public function import(Request $request)
    {
        // 获取上传文件
        $file = $request->file('file');

        if (!$file || !$file->isValid()) {
            return json(['error' => '文件上传失败，或文件无效']);
        }

        // 实例化验证器
//        $validate = new FileUploadValidate();
//
//        // 验证文件
//        $result = $validate->check(['file' => $file]);
//        if (!$result) {
//            // 验证失败，返回错误信息
//            return json(['error' => $validate->getError()]);
//        }

        // 移动文件到临时目录
        $info = $file->move(ROOT_PATH . 'public' . DS . 'uploads');
        if (!$info) {
            return json(['error' => $file->getError()]);
        }

        // 获取文件路径
        $filePath = ROOT_PATH . 'public' . DS . 'uploads' . DS . $info->getSaveName();

        $result = Db::table('test')->insert([
           'path_url' =>  $filePath
        ]);

        if($result < 0){
            return json(['status' => '500', 'message' => '追加临时删除文件表失败', 'data' => $filePath])->code(500);
        }

        $fileExtendName = strtolower( pathinfo($filePath, PATHINFO_EXTENSION) );

        try {
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load($filePath);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                return json(['status' => 'success', 'message' => '操作成功', 'data' => '数据不能为空'])->code(500);
            }

            // 插入数据
            $InserData = [];

            // 遍历行
            for ($row = 3; $row <= $highestRow; $row++) {
                // 获取单元格的值
                $person_name = $sheet->getCell('B' . $row)->getValue();
                $person_code = $sheet->getCell('C' . $row)->getValue();
                $sex = $sheet->getCell('D' . $row)->getValue();
                $entry_time = $sheet->getCell('F' . $row)->getValue();
                $day = Date::excelToDateTimeObject($entry_time);
                $entry_time =  $day->format('Y-m-d H:i:s');
                $positon_name = $sheet->getCell('L' . $row)->getValue();
                $did_name_path = $sheet->getCell('AV' . $row)->getValue();
                $did_name = $sheet->getCell('I' . $row)->getValue();
                $mobile = $sheet->getCell('AB' . $row)->getValue();
                $InserData[] = [
                    'person_name' => $person_name,
                    'person_code' => $person_code,
                    'position_name' => $positon_name,
                    'did_name_path' => $did_name_path,
                    'did_name' => $did_name,
                    'mobile' => $mobile,
                    'sex' => boolval(trim($sex) == '男') ? 1 : 2,
                    'entry_time' => strtotime($entry_time)
                ];
            }

            // 事务处理，确保删除和插入操作的一致性
            $count = 0;
            // 开启事务
            $this->attendDidModel->startTrans();
            try {
                // 执行原生 SQL 清空表
                $delcount = $this->attendDidModel->count();
                Db::query('TRUNCATE TABLE oa_attend_did');

                // 批量插入
                if (!empty($InserData)) {
                    $count = $this->attendDidModel->limit(100)->insertAll($InserData);
                }

                // 提交事务
                $this->attendDidModel->commit();

            } catch (\Exception $e) {
                // 出现错误时回滚事务
                $this->attendDidModel->rollback();
                return json(['status' => 'error', 'message' => '操作失败，错误信息：' . $e->getMessage()])->code(500);
            }

            return json(['status' => 'success', 'message' => '操作成功', 'data' => '共成功导入了' . $count . '条数据，删除了' . $delcount . '条数据'])->code(200);

        } catch (\PhpOffice\PhpSpreadsheet\Reader\Exception $e) {
            return json(['status' => 'success', 'message' => '操作成功', 'data' => '文件格式错误！！'])->code(500);
        }
    }


    // 导入临时排班表
    public function importScheduling(Request $request)
    {
        // 获取上传文件
        $file = $request->file('file');

        if (!$file || !$file->isValid()) {
            return json(['error' => '文件上传失败，或文件无效']);
        }

        // 移动文件到临时目录
        $info = $file->move(ROOT_PATH . 'public' . DS . 'uploads');
        if (!$info) {
            return json(['error' => $file->getError()]);
        }

        // 获取文件路径
        $filePath = ROOT_PATH . 'public' . DS . 'uploads' . DS . $info->getSaveName();

        $result = Db::table('test')->insert([
            'path_url' =>  $filePath
        ]);

        if($result < 0){
            return json(['status' => '500', 'message' => '追加临时删除文件表失败', 'data' => $filePath])->code(500);
        }

        $fileExtendName = strtolower( pathinfo($filePath, PATHINFO_EXTENSION) );

        try {
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load($filePath);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                return json(['status' => 'success', 'message' => '操作成功', 'data' => '数据不能为空'])->code(500);
            }

            // 插入数据
            $InserData = [];

            // 获取一周的日期
            $days = [
                $sheet->getCell('C' . 1)->getValue(),
                $sheet->getCell('D' . 1)->getValue(),
                $sheet->getCell('E' . 1)->getValue(),
                $sheet->getCell('F' . 1)->getValue(),
                $sheet->getCell('G' . 1)->getValue(),
                $sheet->getCell('H' . 1)->getValue(),
                $sheet->getCell('I' . 1)->getValue(),
            ];

            // 定义一个函数用于处理每一天的数据
            function processDayData($sheet, $row, $day, $dayColumn) {
                $did_name = $sheet->getCell('A' . $row)->getValue();
                $person_name = $sheet->getCell('B' . $row)->getValue();
//                $did_name_path = $sheet->getCell('C' . $row)->getValue();
                $is_work_day = $sheet->getCell($dayColumn . $row)->getValue();
                $dayObject = Date::excelToDateTimeObject($day);
                $duty_date = $dayObject->format('Y-m-d');

                if(empty($person_name)){
                    return null;
                }

                return [
                    'person_name' => $person_name,
                    'did_name' => $did_name,
//                    'did_name_path' => $did_name_path,
                    'duty_date' => $duty_date,
                    'is_work_day' => boolval(trim($is_work_day) == '班') ? 1 : 0 ,
                    'create_time' => time(),
                ];
            }

            // 遍历每一天的列并生成数据
            foreach ($days as $index => $day) {
                $dayColumn = chr(67 + $index);  // 将D -> 68，E -> 69，依此类推，代表每一天的列
                for ($row = 2; $row <= $highestRow; $row++) {
                    $data = processDayData($sheet, $row, $day, $dayColumn);

                    if (empty($data)) {
                        // 如果没有记录，则跳过
                        continue;
                    }

                    // 检查数据库中是否已经有相同姓名和时间的记录
                    $existingRecord = $this->checkIfRecordExists($data['person_name'], $data['duty_date']);

                    if ($existingRecord) {
                        // 如果记录已存在，则跳过
                        continue;
                    }

                    $InserData[] = $data;
                }
            }

//            return json(['status' => 'error', 'message' => $InserData])->code(200);

            // 开启事务
            $count = 0;
            $this->attendSchedulingModel->startTrans();
            try {
                // 批量插入
                if (!empty($InserData)) {
                    $count = $this->attendSchedulingModel->limit(100)->insertAll($InserData);
                }
                // 提交事务
                $this->attendSchedulingModel->commit();

            } catch (\Exception $e) {
                // 出现错误时回滚事务
                $this->attendSchedulingModel->rollback();
                return json(['status' => 'error', 'message' => '操作失败，错误信息：' . $e->getMessage()])->code(500);
            }

            return json(['status' => 'success', 'message' => '操作成功', 'data' => '共成功导入了' . $count . '条数据'])->code(200);

        } catch (\PhpOffice\PhpSpreadsheet\Reader\Exception $e) {
            return json(['status' => 'success', 'message' => '操作成功', 'data' => '文件格式错误！！'])->code(500);
        }
    }

    // 检验是否存在同一天同一个人的数据
    public function checkIfRecordExists($person_name, $duty_date)
    {
        // 提前查询所有管理员的id，避免每次查询
        static $adminCache = [];
        if (isset($adminCache[$person_name])) {
            return $adminCache[$person_name];
        }

        $adminId = $this->attendSchedulingModel->where(['person_name' => $person_name,'duty_date' => $duty_date])->value('id');
        $adminCache[$person_name] = $adminId;
        return $adminId;
    }

    // 删除xlsx
    public function delxlsx(){
        $fileCache = Db::table('test')->select();
        $deletedFiles = [];
        $failedFiles = [];

        foreach ($fileCache as $item) {
            // 检查文件是否存在且可写
            if (file_exists($item['path_url']) && is_writable($item['path_url'])) {
                // 尝试删除文件
                if (unlink($item['path_url'])) {
                    $deletedFiles[] = $item['path_url'];  // 记录成功删除的文件
                    // 删除数据库中的对应记录
                    Db::table('test')->where('path_url', $item['path_url'])->delete();
                } else {
                    $failedFiles[] = $item['path_url'];  // 记录删除失败的文件
                }
            } else {
                $failedFiles[] = $item['path_url'];  // 记录不存在或不可写的文件
            }
        }

        // 返回删除结果
        if (empty($deletedFiles)) {
            return json(['status' => 'error', 'message' => '没有文件被删除', 'data' => $failedFiles])->code(500);
        } else {
            return json([
                'status' => 'success',
                'message' => '删除操作完成',
                'data' => [
                    'deleted' => $deletedFiles,
                    'failed' => $failedFiles
                ]
            ])->code(200);
        }
    }
}
